﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAL
{
    public class KHACH_HANGDAL : KetNoiDuLieu
    {
        private string error;
        #region thêm bảng khách hàng
        public void KhachHang_ThemDuLieu(KHACH_HANG kh)
        {
            SqlCommand cmd = new SqlCommand("sp_khachhang_Insert", LayKetNoi());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@email", kh.Email);
            cmd.Parameters.AddWithValue("@mat_khau", kh.MatKhau);
            cmd.Parameters.AddWithValue("@ten_kh", kh.TenKhachHang);
            cmd.Parameters.AddWithValue("@dia_chi", kh.DiaChi);
            cmd.Parameters.AddWithValue("@dien_thoai", kh.DienThoai);
            cmd.ExecuteNonQuery();
        }
        #endregion
        #region sửa bảng khách hàng
        public void KhachHang_SuaDuLieu(KHACH_HANG kh)
        {
            SqlCommand cmd = new SqlCommand("[sp_khachhang_Update]", LayKetNoi());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@MA_KH", kh.MaKhachHang);
            cmd.Parameters.AddWithValue("@EMAIL", kh.Email);
            cmd.Parameters.AddWithValue("@MAT_KHAU", kh.MatKhau);
            cmd.Parameters.AddWithValue("@TEN_KH", kh.TenKhachHang);
            cmd.Parameters.AddWithValue("@DIA_CHI", kh.DiaChi);
            cmd.Parameters.AddWithValue("@DIEN_THOAI", kh.DienThoai);
            cmd.ExecuteNonQuery();
        }
        #endregion
        #region xóa dữ liệu bảng khách hàng
        public void KhachHang_XoaDuLieu(int id)
        {
            SqlCommand cmd = new SqlCommand("sp_khachhang_Delete", LayKetNoi());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@MA_KH", id);
            cmd.ExecuteNonQuery();
        }
        #endregion
        #region Hiển Thị Dữ Liệu
        public KhachHangCollection Get(string Where, int PageIndex, int PageSize, string OrderBy,string OrderDirection, out int TotalRecords)
        {

            SqlCommand cm = new SqlCommand("sp_khachhang_Get", LayKetNoi());
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);
            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            KhachHangCollection khachhangcollection = new KhachHangCollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                KHACH_HANG khachhang = new KHACH_HANG();
                khachhang.MaKhachHang = int.Parse(ds.Tables[0].Rows[i]["MA_KH"].ToString());
                khachhang.Email = ds.Tables[0].Rows[i]["EMAIL"].ToString();
                khachhang.MatKhau = ds.Tables[0].Rows[i]["MAT_KHAU"].ToString();
                khachhang.TenKhachHang = ds.Tables[0].Rows[i]["TEN_KH"].ToString();
                khachhang.DienThoai = ds.Tables[0].Rows[i]["DIEN_THOAI"].ToString();
                khachhang.DiaChi = ds.Tables[0].Rows[i]["DIA_CHI"].ToString();
                khachhangcollection.Add(khachhang);            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
            return khachhangcollection;
        }
        #endregion
        public  KHACH_HANG GetByEmail(string email)
        {
            string sql = "select * from KHACH_HANG where EMAIL='" + email + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            DataTable dt = new DataTable("Table");
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return null;
            }
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];

                KHACH_HANG kh = new KHACH_HANG();
                kh.MaKhachHang = int.Parse(dr["MA_KH"].ToString());
                kh.TenKhachHang = dr["TEN_KH"].ToString();
                kh.Email = dr["EMAIL"].ToString();
                kh.DiaChi = dr["DIA_CHI"].ToString();
                kh.DienThoai = dr["DIEN_THOAI"].ToString();
                kh.MatKhau = dr["MAT_KHAU"].ToString();
                
                return kh;
            }
            return null;
        }
    }
}
